﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;
using System.Web.Configuration;
using MySql.Data.MySqlClient;

namespace DjDal
{
    /// <summary>
    /// MYSQL操作类
    /// </summary>
    public class DbHelper : IDisposable
    {
        #region 私有变量

        private static string _dbkey = "DBConnection";

        private static string _dbConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[_dbkey].ConnectionString;

        private static object lockHelper = new object();

        /// <summary>
        /// Parameters缓存哈希表
        /// </summary>
        private static Hashtable _parameterCache = Hashtable.Synchronized(new Hashtable());

        /// <summary>
        /// 连接字符串列表
        /// </summary>
        private static Dictionary<string, string> _connectionstrings = new Dictionary<string, string>();

        /// <summary>
        /// 数据库链接
        /// </summary>
        private MySqlConnection _connection = null;

        /// <summary>
        /// 数据库事务
        /// </summary>
        private MySqlTransaction _trans = null;

        #endregion

        #region 构造函数

        public DbHelper()
        {
            string s = _dbConnectionString;

            object obj = null;

            if (System.Web.HttpContext.Current != null)
            {
                obj = System.Web.HttpContext.Current.Session != null ? System.Web.HttpContext.Current.Session["DBConnection"] : null;
            }

            if (obj != null)
            {
                s = obj.ToString();
            }

            _connection = new MySqlConnection();
            _connection.ConnectionString = s;
            _connection.Open();
        }

        #endregion

        #region 私有方法


        /// <summary>
        /// 创建MySqlCommand
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private MySqlCommand CreateDbCommand(string commandText, CommandType commandType, params MySqlParameter[] parameters)
        {
            if (_connection == null)
                throw new ArgumentNullException("_connection");
            if (_connection.State == ConnectionState.Closed)
                _connection.Open();

            MySqlCommand command = _connection.CreateCommand();
            command.CommandType = commandType;
            command.CommandText = commandText;
            if (parameters != null)
                AttachParameters(command, parameters);

            return command;
        }

        /// <summary>
        /// 释放MySqlCommand
        /// </summary>
        /// <param name="command"></param>
        private void ReleaseDbCommand(MySqlCommand command)
        {
            if (command != null)
            {
                command.Parameters.Clear();
                command.Dispose();
                command = null;
            }
        }

        /// <summary>
        /// 将MySqlParameter数组分配给MySqlCommand
        /// </summary>
        /// <param name="command">MySqlParameter对象</param>
        /// <param name="parameters">MySqlParameter数组</param>
        private void AttachParameters(MySqlCommand command, MySqlParameter[] parameters)
        {
            if (command == null)
                throw new ArgumentNullException("command");

            if (parameters == null)
                throw new ArgumentNullException("parameters");

            foreach (MySqlParameter para in parameters)
            {
                if (para != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((para.Direction == ParameterDirection.InputOutput || para.Direction == ParameterDirection.Input)
                        && (para.Value == null))
                    {
                        para.Value = DBNull.Value;
                    }
                    command.Parameters.Add(para);
                }
            }
        }

        #endregion

        #region 属性

        #endregion

        #region 事务

        /// <summary>
        /// 事务开始
        /// </summary>
        public void BeginTransaction()
        {
            if (_trans == null)
            {
                _trans = _connection.BeginTransaction();
            }
        }

        /// <summary>
        /// 事务提交
        /// </summary>
        public void Commit()
        {
            if (_trans != null)
            {
                _trans.Commit();
                _trans.Dispose();
                _trans = null;
            }
        }

        /// <summary>
        /// 事务回滚
        /// </summary>
        public void Rollback()
        {
            if (_trans != null)
            {
                _trans.Rollback();
                _trans.Dispose();
                _trans = null;
            }
        }

        #endregion

        #region ExecuteNonQuery

        public int ExecuteNonQuery(string commandText)
        {
            return ExecuteNonQuery(commandText, CommandType.Text);
        }

        public int ExecuteNonQuery(string commandText, CommandType commandType)
        {
            return ExecuteNonQuery(commandText, commandType, null);
        }

        public int ExecuteNonQuery(string commandText, params MySqlParameter[] parameters)
        {
            return ExecuteNonQuery(commandText, CommandType.Text, parameters);
        }

        public int ExecuteNonQuery(string commandText, CommandType commandType, params MySqlParameter[] parameters)
        {
            MySqlCommand command = CreateDbCommand(commandText, commandType, parameters);

            int ret = command.ExecuteNonQuery();

            ReleaseDbCommand(command);

            return ret;
        }

        #endregion

        #region ExecuteNonQuery GetScopeId

        public int ExecuteNonQuery(out object pid, string commandText)
        {
            return ExecuteNonQuery(out pid, commandText);
        }

        public int ExecuteNonQuery(out object pid, string commandText, CommandType commandType)
        {
            return ExecuteNonQuery(out pid, commandText, commandType, null);
        }

        public int ExecuteNonQuery(out object pid, string commandText, params MySqlParameter[] parameters)
        {
            return ExecuteNonQuery(out pid, commandText, CommandType.Text, parameters);
        }

        public int ExecuteNonQuery(out object pid, string commandText, CommandType commandType, params MySqlParameter[] parameters)
        {
            MySqlCommand command = CreateDbCommand(commandText, commandType, parameters);

            int ret = command.ExecuteNonQuery();

            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT LAST_INSERT_ID();";

            pid = command.ExecuteScalar();

            ReleaseDbCommand(command);

            return ret;
        }

        #endregion

        #region ExecuteReader

        public MySqlDataReader ExecuteReader(string commandText)
        {
            return ExecuteReader(commandText, CommandType.Text);
        }

        public MySqlDataReader ExecuteReader(string commandText, CommandType commandType)
        {
            return ExecuteReader(commandText, commandType, null);
        }

        public MySqlDataReader ExecuteReader(string commandText, params MySqlParameter[] parameters)
        {
            return ExecuteReader(commandText, CommandType.Text, parameters);
        }

        public MySqlDataReader ExecuteReader(string commandText, CommandType commandType, params MySqlParameter[] parameters)
        {
            MySqlCommand command = CreateDbCommand(commandText, commandType, parameters);

            MySqlDataReader reader = command.ExecuteReader();

            ReleaseDbCommand(command);

            return reader;
        }

        #endregion

        #region ExecuteScalar

        public object ExecuteScalar(string commandText)
        {
            return ExecuteScalar(commandText, CommandType.Text);
        }

        public object ExecuteScalar(string commandText, CommandType commandType)
        {
            return ExecuteScalar(commandText, commandType, null);
        }

        public object ExecuteScalar(string commandText, params MySqlParameter[] parameters)
        {
            return ExecuteScalar(commandText, CommandType.Text, parameters);
        }

        public object ExecuteScalar(string commandText, CommandType commandType, params MySqlParameter[] parameters)
        {
            MySqlCommand command = CreateDbCommand(commandText, commandType, parameters);

            object ret = command.ExecuteScalar();

            ReleaseDbCommand(command);

            return ret;
        }

        #endregion

        #region ExecuteDataset

        public DataSet ExecuteDataset(string commandText)
        {
            return ExecuteDataset(commandText, CommandType.Text);
        }

        public DataSet ExecuteDataset(string commandText, CommandType commandType)
        {
            return ExecuteDataset(commandText, commandType, null);
        }

        public DataSet ExecuteDataset(string commandText, params MySqlParameter[] parameters)
        {
            return ExecuteDataset(commandText, CommandType.Text, parameters);
        }

        public DataSet ExecuteDataset(string commandText, CommandType commandType, params MySqlParameter[] parameters)
        {
            MySqlCommand command = CreateDbCommand(commandText, commandType, parameters);

            using (MySqlDataAdapter da = new MySqlDataAdapter())
            {
                da.SelectCommand = command;
                DataSet ds = new DataSet();
                da.Fill(ds);

                ReleaseDbCommand(command);

                return ds;
            }
        }

        #endregion

        #region ExecuteDatatable

        public DataTable ExecuteDatatable(string commandText)
        {
            return ExecuteDatatable(commandText, CommandType.Text);
        }

        public DataTable ExecuteDatatable(string commandText, CommandType commandType)
        {
            return ExecuteDatatable(commandText, commandType, null);
        }

        public DataTable ExecuteDatatable(string commandText, params MySqlParameter[] parameters)
        {
            return ExecuteDatatable(commandText, CommandType.Text, parameters);
        }

        public DataTable ExecuteDatatable(string commandText, CommandType commandType, params MySqlParameter[] parameters)
        {
            DataSet ds = ExecuteDataset(commandText, commandType, parameters);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return null;
        }

        #endregion

        #region 生成参数

        /// <summary>
        /// 生成参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">数据类型</param>
        /// <param name="size">大小</param>
        /// <param name="direction">方向 in or out</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        static public MySqlParameter MakeParameter(string paramName, MySqlDbType dbType, int size, ParameterDirection direction, object value)
        {
            MySqlParameter param = new MySqlParameter();
            param.ParameterName = paramName;
            param.MySqlDbType = dbType;
            param.Direction = direction;

            if (size > 0)
                param.Size = size;

            if (!(direction == ParameterDirection.Output && value == null))
                param.Value = value;

            return param;
        }

        /// <summary>
        /// 生成输入参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        static public MySqlParameter MakeInParameter(string paramName, object value)
        {
            return new MySqlParameter(paramName, value);
        }

        /// <summary>
        /// 生成输入参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        static public MySqlParameter MakeInParameter(string paramName, MySqlDbType dbType, object value)
        {
            return MakeParameter(paramName, dbType, 0, ParameterDirection.Input, value);
        }

        /// <summary>
        /// 生成输入参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">参数大小</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        static public MySqlParameter MakeInParameter(string paramName, MySqlDbType dbType, int size, object value)
        {
            return MakeParameter(paramName, dbType, size, ParameterDirection.Input, value);
        }

        /// <summary>
        /// 生成输出参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <returns></returns>
        static public MySqlParameter MakeOutParameter(string paramName, MySqlDbType dbType, object val)
        {
            return MakeParameter(paramName, dbType, 0, ParameterDirection.Output, val);
        }

        /// <summary>
        /// 生成输出参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">参数大小</param>
        /// <returns></returns>
        static public MySqlParameter MakeOutParameter(string paramName, MySqlDbType dbType, int size, object val)
        {
            return MakeParameter(paramName, dbType, size, ParameterDirection.Output, val);
        }

        /// <summary>
        /// 生成返回参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <returns></returns>
        static public MySqlParameter MakeReturnParameter(string paramName, MySqlDbType dbType, object val)
        {
            return MakeParameter(paramName, dbType, 0, ParameterDirection.ReturnValue, val);
        }

        /// <summary>
        /// 生成返回参数
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">参数类型</param>
        /// <param name="size">参数大小</param>
        /// <returns></returns>
        static public MySqlParameter MakeReturnParameter(string paramName, MySqlDbType dbType, int size, object val)
        {
            return MakeParameter(paramName, dbType, size, ParameterDirection.ReturnValue, val);
        }

        #endregion

        #region 缓存参数

        /// <summary>
        /// 添加参数到缓存
        /// </summary>
        /// <param name="key"></param>
        /// <param name="parameters"></param>
        static public void CacheParameters(string key, params MySqlParameter[] parameters)
        {
            _parameterCache[key] = parameters;
        }

        /// <summary>
        /// 从缓存获取参数
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        static public MySqlParameter[] GetCachedParameters(string key)
        {
            MySqlParameter[] cachedParms = (MySqlParameter[])_parameterCache[key];

            if (cachedParms == null)
                return null;

            MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        #endregion

        #region 关闭链接

        /// <summary>
        /// 关闭链接
        /// </summary>
        public void Close()
        {
            if (_trans != null)
            {
                _trans.Dispose();
                _trans = null;
            }
            if (_connection != null && _connection.State == ConnectionState.Open)
            {
                _connection.Close();
                _connection.Dispose();
                _connection = null;
            }
        }

        #endregion

        #region 切换连接字符串

        public static void ToggleConnection(string connectionStringName)
        {
            if (connectionStringName == _dbkey)
            {
                System.Web.HttpContext.Current.Session.Remove("DBConnection");
                return;
            }

            string connectionString = string.Empty;

            if (_connectionstrings.ContainsKey(connectionStringName))
            {
                connectionString = _connectionstrings[connectionStringName];
            }
            else
            {
                connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;

                lock (lockHelper)
                {
                    _connectionstrings.Add(connectionStringName, connectionString);
                }
            }
            System.Web.HttpContext.Current.Session["DBConnection"] = connectionString;
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            this.Close();
        }

        #endregion
    }
}
